package beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.NamingException;

import tools.DBManager;

public class OrderList {
	private ArrayList<OrderBean> orders;
	
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	public void setOrders(ArrayList<OrderBean> orders) {
		this.orders = orders;
	}
	public ArrayList<OrderBean> getOrders(){
		return orders;
	}
	
	public OrderList(){
		
	}
	
	public ArrayList<OrderBean> setOrdersByUserID(int userID){		
		try 
		{
			orders = new ArrayList<OrderBean>();
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format("SELECT * FROM t_order where t_order.UserID = %d", userID);
			rs = stmt.executeQuery(sqlCmd);

			// fill the list
			while (rs.next()) {
				OrderBean order = new OrderBean();
				
				order.setID(rs.getInt("ID"));
				order.setUserID(rs.getInt("UserID"));
				order.setStatus(rs.getInt("Status"));
				order.setProductString(rs.getString("Products"));
				order.setRecipient(rs.getString("Recipient"));
				order.setAddress(rs.getString("Address"));
				order.setPhone(rs.getString("Phone"));
				order.setOrdertime(rs.getTimestamp("OrderTime"));
				order.setDeliveryTime(rs.getTimestamp("DeliveryTime"));

				orders.add(order);
			}

		} catch (SQLException sqlException) {

		} catch (NamingException namingException) {

		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
		return orders;
	}
	
	public ArrayList<OrderBean> setAllOrders(){		
		try 
		{
			orders = new ArrayList<OrderBean>();
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format("SELECT * FROM t_order");
			rs = stmt.executeQuery(sqlCmd);

			// fill the list
			while (rs.next()) {
				OrderBean order = new OrderBean();
				
				order.setID(rs.getInt("ID"));
				order.setUserID(rs.getInt("UserID"));
				order.setStatus(rs.getInt("Status"));
				order.setProductString(rs.getString("Products"));
				order.setRecipient(rs.getString("Recipient"));
				order.setAddress(rs.getString("Address"));
				order.setPhone(rs.getString("Phone"));
				order.setOrdertime(rs.getTimestamp("OrderTime"));
				order.setDeliveryTime(rs.getTimestamp("DeliveryTime"));

				orders.add(order);
			}

		} catch (SQLException sqlException) {

		} catch (NamingException namingException) {

		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
		return orders;
	}
	
	public ArrayList<OrderBean> setUndeliveryedOrder(){		
		try 
		{
			orders = new ArrayList<OrderBean>();
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format("SELECT * FROM t_order where t_order.Status = 0");
			rs = stmt.executeQuery(sqlCmd);

			// fill the list
			while (rs.next()) {
				OrderBean order = new OrderBean();
				
				order.setID(rs.getInt("ID"));
				order.setUserID(rs.getInt("UserID"));
				order.setStatus(rs.getInt("Status"));
				order.setProductString(rs.getString("Products"));
				order.setRecipient(rs.getString("Recipient"));
				order.setAddress(rs.getString("Address"));
				order.setPhone(rs.getString("Phone"));
				order.setOrdertime(rs.getTimestamp("OrderTime"));
				order.setDeliveryTime(rs.getTimestamp("DeliveryTime"));

				orders.add(order);
			}

		} catch (SQLException sqlException) {

		} catch (NamingException namingException) {

		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
		return orders;
	}
	
}
